
-----------------------------------------------------------------------
-- lp_vv_Pracownicy
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_lp_vv_Pracownicy_w_lp_vv_WizPrac')
    ALTER TABLE lp_vv_Pracownicy DROP CONSTRAINT FK_lp_vv_Pracownicy_w_lp_vv_WizPrac;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'lp_vv_Pracownicy')
BEGIN
    DECLARE @reftable_1 nvarchar(60), @constraintname_1 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'lp_vv_Pracownicy'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_1, @constraintname_1
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_1+' drop constraint '+@constraintname_1)
        FETCH NEXT from refcursor into @reftable_1, @constraintname_1
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE lp_vv_Pracownicy
END

CREATE TABLE lp_vv_Pracownicy
(
    lp_PracownicyId INT NOT NULL IDENTITY,
    LpLogo VARCHAR(15) NOT NULL,
    Imie VARCHAR(20) NOT NULL,
    Nazwisko VARCHAR(40) NOT NULL,
    CONSTRAINT lp_vv_Pracownicy_PK PRIMARY KEY (lp_PracownicyId)
);

CREATE INDEX PK_lp_vv_Pracownicy ON lp_vv_Pracownicy (lp_PracownicyId);

BEGIN
ALTER TABLE lp_vv_Pracownicy ADD CONSTRAINT FK_lp_vv_Pracownicy_w_lp_vv_WizPrac FOREIGN KEY (LpLogo) REFERENCES w_lp_vv_WizPrac (NR_OS)
END
;

-----------------------------------------------------------------------
-- w_lp_vv_WizJedn
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_w_lp_vv_WizJedn_w_lp_vv_WizJedn')
    ALTER TABLE w_lp_vv_WizJedn DROP CONSTRAINT FK_w_lp_vv_WizJedn_w_lp_vv_WizJedn;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'w_lp_vv_WizJedn')
BEGIN
    DECLARE @reftable_2 nvarchar(60), @constraintname_2 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'w_lp_vv_WizJedn'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_2, @constraintname_2
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_2+' drop constraint '+@constraintname_2)
        FETCH NEXT from refcursor into @reftable_2, @constraintname_2
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE w_lp_vv_WizJedn
END

CREATE TABLE w_lp_vv_WizJedn
(
    ID_JEDN VARCHAR(30) NOT NULL,
    ID_NAD_JEDN VARCHAR(30) NULL,
    NR_OS_KIER VARCHAR(15) NULL,
    NAZWA_JEDN VARCHAR(100) NOT NULL,
    NAZWA_JEDN_ANG VARCHAR(100) NOT NULL,
    CONSTRAINT w_lp_vv_WizJedn_PK PRIMARY KEY (ID_JEDN)
);

CREATE INDEX PK_w_lp_vv_WizJedn ON w_lp_vv_WizJedn (ID_JEDN);

BEGIN
ALTER TABLE w_lp_vv_WizJedn ADD CONSTRAINT FK_w_lp_vv_WizJedn_w_lp_vv_WizJedn FOREIGN KEY (ID_NAD_JEDN) REFERENCES w_lp_vv_WizJedn (ID_JEDN)
END
;

-----------------------------------------------------------------------
-- w_lp_vv_WizStan
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'w_lp_vv_WizStan')
BEGIN
    DECLARE @reftable_3 nvarchar(60), @constraintname_3 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'w_lp_vv_WizStan'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_3, @constraintname_3
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_3+' drop constraint '+@constraintname_3)
        FETCH NEXT from refcursor into @reftable_3, @constraintname_3
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE w_lp_vv_WizStan
END

CREATE TABLE w_lp_vv_WizStan
(
    LpLogo VARCHAR(15) NOT NULL,
    ID_STAN VARCHAR(30) NOT NULL,
    NAZWA_STAN VARCHAR(100) NOT NULL,
    NAZWA_STAN_ANG VARCHAR(100) NOT NULL,
    GRADE INT NULL,
    CONSTRAINT w_lp_vv_WizStan_PK PRIMARY KEY (LpLogo)
);

CREATE INDEX PK_w_lp_vv_WizStan ON w_lp_vv_WizStan (LpLogo);

-----------------------------------------------------------------------
-- w_lp_vv_WizPrac
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_w_lp_vv_WizPrac_w_lp_vv_WizStan')
    ALTER TABLE w_lp_vv_WizPrac DROP CONSTRAINT FK_w_lp_vv_WizPrac_w_lp_vv_WizStan;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_w_lp_vv_WizPrac_w_lp_vv_WizJedn')
    ALTER TABLE w_lp_vv_WizPrac DROP CONSTRAINT FK_w_lp_vv_WizPrac_w_lp_vv_WizJedn;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'w_lp_vv_WizPrac')
BEGIN
    DECLARE @reftable_4 nvarchar(60), @constraintname_4 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'w_lp_vv_WizPrac'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_4, @constraintname_4
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_4+' drop constraint '+@constraintname_4)
        FETCH NEXT from refcursor into @reftable_4, @constraintname_4
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE w_lp_vv_WizPrac
END

CREATE TABLE w_lp_vv_WizPrac
(
    NR_OS VARCHAR(15) NOT NULL,
    ID_STAN VARCHAR(30) NOT NULL,
    ID_JEDN VARCHAR(30) NOT NULL,
    EMAIL VARCHAR(200) NULL,
    CONSTRAINT w_lp_vv_WizPrac_PK PRIMARY KEY (NR_OS,ID_JEDN)
);

CREATE INDEX PK_w_lp_vv_WizPrac ON w_lp_vv_WizPrac (NR_OS);

BEGIN
ALTER TABLE w_lp_vv_WizPrac ADD CONSTRAINT FK_w_lp_vv_WizPrac_w_lp_vv_WizStan FOREIGN KEY (NR_OS) REFERENCES w_lp_vv_WizStan (LpLogo)
END
;

BEGIN
ALTER TABLE w_lp_vv_WizPrac ADD CONSTRAINT FK_w_lp_vv_WizPrac_w_lp_vv_WizJedn FOREIGN KEY (ID_JEDN) REFERENCES w_lp_vv_WizJedn (ID_JEDN)
END
;

-----------------------------------------------------------------------
-- lp_vv_UmowyStanowiska
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='FK_lp_vv_UmowyStanowiska_w_lp_vv_WizStan')
    ALTER TABLE lp_vv_UmowyStanowiska DROP CONSTRAINT FK_lp_vv_UmowyStanowiska_w_lp_vv_WizStan;

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'lp_vv_UmowyStanowiska')
BEGIN
    DECLARE @reftable_5 nvarchar(60), @constraintname_5 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'lp_vv_UmowyStanowiska'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_5, @constraintname_5
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_5+' drop constraint '+@constraintname_5)
        FETCH NEXT from refcursor into @reftable_5, @constraintname_5
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE lp_vv_UmowyStanowiska
END

CREATE TABLE lp_vv_UmowyStanowiska
(
    lp_UmowyStanowiskaId BIGINT(8) NOT NULL,
    DataOd DATETIME(16,3) NULL,
    DataDo DATETIME(16,3) NULL,
    Stanowisko VARCHAR(30) NULL,
    LpLogo VARCHAR(15) NULL,
    CONSTRAINT lp_vv_UmowyStanowiska_PK PRIMARY KEY (lp_UmowyStanowiskaId)
);

CREATE INDEX PK_lp_vv_UmowyStanowiska ON lp_vv_UmowyStanowiska (lp_UmowyStanowiskaId);

BEGIN
ALTER TABLE lp_vv_UmowyStanowiska ADD CONSTRAINT FK_lp_vv_UmowyStanowiska_w_lp_vv_WizStan FOREIGN KEY (LpLogo,Stanowisko) REFERENCES w_lp_vv_WizStan (LpLogo,ID_STAN)
END
;

-----------------------------------------------------------------------
-- lp_vv_Stanowiska
-----------------------------------------------------------------------

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'lp_vv_Stanowiska')
BEGIN
    DECLARE @reftable_6 nvarchar(60), @constraintname_6 nvarchar(60)
    DECLARE refcursor CURSOR FOR
    select reftables.name tablename, cons.name constraintname
        from sysobjects tables,
            sysobjects reftables,
            sysobjects cons,
            sysreferences ref
        where tables.id = ref.rkeyid
            and cons.id = ref.constid
            and reftables.id = ref.fkeyid
            and tables.name = 'lp_vv_Stanowiska'
    OPEN refcursor
    FETCH NEXT from refcursor into @reftable_6, @constraintname_6
    while @@FETCH_STATUS = 0
    BEGIN
        exec ('alter table '+@reftable_6+' drop constraint '+@constraintname_6)
        FETCH NEXT from refcursor into @reftable_6, @constraintname_6
    END
    CLOSE refcursor
    DEALLOCATE refcursor
    DROP TABLE lp_vv_Stanowiska
END

CREATE TABLE lp_vv_Stanowiska
(
    Stanowisko VARCHAR(30) NOT NULL,
    StanowiskoRodzaj VARCHAR(100) NULL,
    Nazwa VARCHAR(100) NOT NULL,
    NazwaObca VARCHAR(100) NOT NULL,
    CONSTRAINT lp_vv_Stanowiska_PK PRIMARY KEY (Stanowisko)
);

CREATE INDEX PK_lp_vv_Stanowiska ON lp_vv_Stanowiska (Stanowisko);
